﻿using NetByDocker.Core.Npoi.Attributes;
using NetByDocker.Core.Npoi.Dto;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.ComponentModel;
using System.Data;
using System.Reflection;

namespace NetByDocker.Core.Npoi;

/// <summary>
///  Excel操作类(普通通用款)
/// </summary>
public class NpoiHelper
{
    /// <summary>
    /// 导出Excel
    /// </summary>
    /// <param name="data"></param>
    /// <param name="columns">Excel和T属性映射关系{propName,columnName}</param>
    /// <param name="sheetName"></param>
    /// <returns></returns>
    public static byte[] ExportExcel<T>(List<T>? data, Dictionary<string, string> columns, string? sheetName = null)
    {
        if (data == null || columns.Count <= 0)
            return Array.Empty<byte>();

        using var workbook = new XSSFWorkbook();
        var sheet = workbook.CreateSheet(sheetName ?? "Sheet1");
        ExportToSheet(sheet, data, columns);
        var ms = new MemoryStream();
        workbook.Write(ms);
        return ms.ToArray();
    }

    /// <summary>
    /// 导出Excel
    /// </summary>
    /// <param name="data"></param>
    /// <param name="sheetName"></param>
    /// <returns></returns>
    public static byte[] ExportExcel<T>(List<T>? data, string? sheetName = null)
    {
        if (data == null)
            return Array.Empty<byte>();

        using var workbook = new XSSFWorkbook();
        var sheet = workbook.CreateSheet(sheetName ?? "Sheet1");
        ExportToSheet(sheet, data);
        using var ms = new MemoryStream();
        workbook.Write(ms);
        return ms.ToArray();
    }

    /// <summary>
    /// 导出空的excel模板
    /// </summary>
    /// <param name="sheetName"></param>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public static byte[] ExportTemplate<T>(string? sheetName = null)
    {
        using var workbook = new XSSFWorkbook();
        var sheet = workbook.CreateSheet(sheetName ?? "Sheet1");
        ExportEmptyTemplateToSheet<T>(sheet);
        var ms = new MemoryStream();
        workbook.Write(ms);
        return ms.ToArray();
    }

    /// <summary>
    /// 从Excel解析数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="excelStream"></param>
    /// <param name="columns">Excel和T属性映射关系{propName,columnName}</param>
    /// <param name="headerIndex"></param>
    /// <returns></returns>F
    public List<T> ImportExcel<T>(Stream? excelStream, Dictionary<string, string> columns, int headerIndex = 0)
        where T : new()
    {
        columns ??= new Dictionary<string, string>();
        if (excelStream == null || columns.Count == 0)
        {
            return new List<T>();
        }

        var list = new List<T>();

        using var workBook = CreateWorkbook(excelStream);
        var sheet = workBook.GetSheetAt(0);

        var dicIndexer = GetPropertyInfoIndexer<T>(sheet.GetRow(headerIndex), columns);

        var lastRowNum = sheet.LastRowNum;

        for (var i = headerIndex + 1; i <= lastRowNum; i++)
        {
            var row = sheet.GetRow(i);
            if (row == null)
                continue;

            T obj = new();
            foreach (var item in dicIndexer)
            {
                try
                {
                    object val = GetCellValue(row.GetCell(item.Key), item.Value.PropertyType);
                    if (val != null)
                    {
                        item.Value.SetValue(obj, val, null); //属性赋值
                    }
                }
                catch { }
            }

            list.Add(obj);
        }

        return list;
    }

    /// <summary>
    /// 导出Excel(mutiple sheets）
    /// </summary>
    /// <param name="dtList"></param>
    /// <param name="sheetColumns"></param>
    /// <returns></returns>
    public byte[] ExportMultiple(List<DataTable> dtList, Dictionary<string, Dictionary<string, string>> sheetColumns)
    {
        using IWorkbook workbook = new XSSFWorkbook();
        sheetColumns ??= new Dictionary<string, Dictionary<string, string>>();
        if (dtList != null && sheetColumns.Count > 0)
        {
            if (sheetColumns.Count > 0)
            {
                int i = 0;
                foreach (var name in sheetColumns)
                {
                    ISheet sheet = workbook.CreateSheet(name.Key ?? "Sheet1");
                    ExportToMultipleSheet(sheet, dtList[i], name.Value);
                    i++;
                }
            }

            var ms = new MemoryStream();
            workbook.Write(ms);
            return ms.ToArray();
        }
        else
        {
            if (sheetColumns.Count > 0)
            {
                int i = 0;
                foreach (var name in sheetColumns)
                {
                    ISheet sheet = workbook.CreateSheet(name.Key ?? "Sheet1");
                    ExportToEmptySheet(sheet, name.Value);
                    i++;
                }
            }

            var ms = new MemoryStream();
            workbook.Write(ms);
            return ms.ToArray();
        }
    }

    /// <summary>
    /// 列表转为DataTable
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="data"></param>
    /// <returns></returns>
    public DataTable ToDataTable<T>(List<T> data)
    {
        DataTable table = new();
        if (data?.Count > 0)
        {
            var props = TypeDescriptor.GetProperties(typeof(T));
            for (int i = 0; i < props.Count; i++)
            {
                PropertyDescriptor prop = props[i];
                table.Columns.Add(prop.Name, prop.PropertyType);
            }

            object[] values = new object[props.Count];
            foreach (T item in data)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = props[i].GetValue(item);
                }

                table.Rows.Add(values);
            }
        }

        return table;
    }

    /// <summary>
    /// 将excel导入到datatable
    /// </summary>
    /// <param name="filePath">excel路径</param>
    /// <param name="dataRowIndex">数据起始行下标</param>
    /// <param name="titleRowIndex">标题起始行下标</param>
    /// <returns>返回datatable</returns>
    public DataTable ExcelToDataTable(string filePath, int dataRowIndex = 1, int titleRowIndex = 0)
    {
        DataTable ExcelTable = null;
        FileStream fs = null;
        IWorkbook workbook = null;
        ISheet sheet = null;
        try
        {
            using (fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                // 解决版本兼容
                workbook = CreateWorkbook(fs);
                if (workbook == null)
                    return new DataTable();

                sheet = workbook.GetSheetAt(0); //读取第一个sheet
                ExcelTable = new DataTable();
                if (sheet == null)
                    return new DataTable();

                int rowCount = sheet.LastRowNum; //总行数
                if (rowCount == 0)
                    return new DataTable();

                IRow firstRow = sheet.GetRow(titleRowIndex); //第一行
                int cellCount = firstRow.LastCellNum; //列数

                //创建datatable的列
                for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                {
                    ICell cell = firstRow.GetCell(i);
                    if (cell?.StringCellValue != null)
                    {
                        var column = new DataColumn(cell.StringCellValue);
                        ExcelTable.Columns.Add(column);
                    }
                }

                //填充datatable行
                for (int i = dataRowIndex; i <= rowCount; ++i)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null) continue;

                    DataRow dataRow = ExcelTable.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell == null)
                        {
                            dataRow[j] = "";
                        }
                        else
                        {
                            switch (cell.CellType)
                            {
                                case CellType.Blank:
                                    dataRow[j] = "";
                                    break;

                                case CellType.Numeric:
                                    short format = cell.CellStyle.DataFormat;

                                    //对时间格式的处理
                                    if (format == 14 || format == 31 || format == 57 || format == 58)
                                        dataRow[j] = cell.DateCellValue;
                                    else if (format == 20)
                                        dataRow[j] = cell.DateCellValue;
                                    else
                                        dataRow[j] = cell.NumericCellValue;
                                    break;

                                case CellType.String:
                                    dataRow[j] = cell.StringCellValue;
                                    break;
                            }
                        }
                    }

                    ExcelTable.Rows.Add(dataRow);
                }
            }

            //由于excel表在删除一张表的时候回再次读取回出现空行的原因
            //所以需要一个删除空行的方法⇣⇣⇣⇣
            List<DataRow> removelist = new();
            for (int i = 0; i < ExcelTable.Rows.Count; i++)
            {
                bool IsNull = true;
                for (int j = 0; j < ExcelTable.Columns.Count; j++)
                {
                    if (!string.IsNullOrEmpty(ExcelTable.Rows[i][j].ToString().Trim()))
                    {
                        IsNull = false;
                    }
                }

                if (IsNull)
                {
                    removelist.Add(ExcelTable.Rows[i]);
                }
            }

            for (int i = 0; i < removelist.Count; i++)
            {
                ExcelTable.Rows.Remove(removelist[i]);
            }

            removelist.Clear();

            return ExcelTable;
        }
        catch (Exception)
        {
            fs?.Close();
            return null;
        }
    }

    #region 私有方法

    /// <summary>
    ///创建Workbook
    /// </summary>
    /// <param name="stream"></param>
    /// <returns></returns>
    private IWorkbook CreateWorkbook(Stream stream)
    {
        try
        {
            return new XSSFWorkbook(stream); //07
        }
        catch
        {
            return new HSSFWorkbook(stream); //03
        }
    }

    /// <summary>
    /// 获取单元格的值
    /// </summary>
    /// <param name="cell"></param>
    /// <param name="valueType"></param>
    /// <returns></returns>
    private object GetCellValue(ICell cell, Type valueType)
    {
        if (cell == null) return null;
        var value = cell.ToString(); //cell.ToString()即可获取string Value
        if (cell.CellType == CellType.Numeric && valueType.AssemblyQualifiedName.Contains("System.DateTime"))
        {
            if (cell.DateCellValue.Value == new DateTime(1900, 1, 1)) return null;
            return cell.DateCellValue;
        }

        if (!string.IsNullOrEmpty(value))
        {
            return Convert.ChangeType(value, valueType);
        }

        return value;
    }

    /// <summary>
    /// 属性和Excel Column索引关系
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="row"></param>
    /// <param name="columns"></param>
    /// <returns></returns>
    private Dictionary<int, PropertyInfo> GetPropertyInfoIndexer<T>(IRow row,
                                                                    Dictionary<string, string> columns)
    {
        var dicIndexer = new Dictionary<int, PropertyInfo>();

        if (row == null)
        {
            throw new Exception("header row is null");
        }

        var props = typeof(T).GetProperties();
        for (int i = 0; i < row.LastCellNum; i++)
        {
            var cellValue = row.Cells[i].StringCellValue;
            if (cellValue != null)
            {
                foreach (var item in columns)
                {
                    if (string.Equals(item.Value, cellValue, StringComparison.CurrentCultureIgnoreCase))
                    {
                        //匹配属性
                        var prop = Array.Find(props,
                            p => string.Equals(p.Name, item.Key, StringComparison.CurrentCultureIgnoreCase));
                        if (prop != null)
                        {
                            dicIndexer.Add(i, prop);
                        }

                        break;
                    }
                }
            }
        }

        return dicIndexer;
    }

    /// <summary>
    /// 导出到单元簿
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sheet"></param>
    /// <param name="data"></param>
    /// <param name="columns"></param>
    private static void ExportToSheet<T>(ISheet sheet, IReadOnlyList<T> data, Dictionary<string, string> columns)
    {
        var headerRow = sheet.CreateRow(0);

        var dicIndexer = new Dictionary<int, PropertyInfo>();
        var props = typeof(T).GetProperties();

        //设置标题行
        var i = 0;
        foreach (var item in columns)
        {
            headerRow.CreateCell(i).SetCellValue(item.Value);

            //匹配属性
            var prop = Array.Find(props,
                p => string.Equals(p.Name, item.Key, StringComparison.CurrentCultureIgnoreCase));
            if (prop != null)
            {
                dicIndexer.Add(i, prop);
                i++;
            }
        }

        //填充数据
        for (i = 0; i < data.Count; i++)
        {
            sheet.AutoSizeColumn(i);
            var row = sheet.CreateRow(i + 1);
            foreach (var item in dicIndexer)
            {
                var value = item.Value.GetValue(data[i]);
                if (value != null)
                    row.CreateCell(item.Key).SetCellValue(value.ToString());
            }
        }
    }

    /// <summary>
    /// 导出到单元簿
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sheet"></param>
    /// <param name="data"></param>
    private static void ExportToSheet<T>(ISheet sheet, IReadOnlyList<T> data)
    {
        var columnProperties = typeof(T).GetProperties()
                                        .Where(t => t.GetCustomAttribute(typeof(IgnoreColumnAttribute)) is null)
                                        .Select(t => new ColumnProperty(t))
                                        .ToList();

        //设置标题行
        var i = 0;
        var headerRow = sheet.CreateRow(0);
        foreach (var item in columnProperties)
        {
            headerRow.CreateCell(i).SetCellValue(item.Name);
            i++;
        }

        //填充数据
        for (i = 1; i <= data.Count; i++)
        {
            var row = sheet.CreateRow(i);

            var j = 0;
            foreach (var pro in columnProperties)
            {
                row.CreateCell(j).SetCellValue(pro.GetCellValue(data[i - 1]));
                j++;
            }
        }
    }

    /// <summary>
    /// 导出到单元簿
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sheet"></param>
    private static void ExportEmptyTemplateToSheet<T>(ISheet sheet)
    {
        var headerRow = sheet.CreateRow(0);
        var i = 0;
        var props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance)
                             .OrderBy(p => p.MetadataToken)
                             .ToArray();
        foreach (var item in props)
        {
            var obs = (DescriptionAttribute)item.GetCustomAttribute(typeof(DescriptionAttribute), false);
            headerRow.CreateCell(i).SetCellValue(obs is null ? item.Name : obs.Description);
            i++;
        }
    }

    /// <summary>
    /// 导出空的单元簿
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="columns"></param>
    private void ExportToEmptySheet(ISheet sheet, Dictionary<string, string> columns)
    {
        var headerRow = sheet.CreateRow(0);

        //设置标题行
        var i = 0;
        foreach (var item in columns)
        {
            headerRow.CreateCell(i).SetCellValue(item.Value);
            i++;
        }
    }

    /// <summary>
    /// 导出多个单元薄
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="data"></param>
    /// <param name="columns"></param>
    private void ExportToMultipleSheet(ISheet sheet, DataTable data, Dictionary<string, string> columns)
    {
        //设置标题行
        var i = 0;
        IRow headerRow = sheet.CreateRow(0);
        foreach (var item in columns)
        {
            headerRow.CreateCell(i).SetCellValue(item.Value);
            i++;
        }

        int count = 1;

        //填充数据
        for (i = 0; i < data.Rows.Count; i++)
        {
            var row = sheet.CreateRow(0);
            int j;
            for (j = 0; j < data.Columns.Count; ++j)
            {
                row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
            }

            ++count;
        }
    }

    #endregion
}